package hse.jack.module;

import hse.jack.model.BaseProblem;
import hse.jack.model.JobInfos;
import hse.jack.model.TrainSubject;
import hse.jack.util.DwzUtil;
import hse.jack.util.WordDocumentUtil;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLDecoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.nutz.mvc.annotation.AdaptBy;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.Param;
import org.nutz.mvc.upload.TempFile;
import org.nutz.mvc.upload.UploadAdaptor;
import org.nutz.service.EntityService;

/**
 * 
 * <b>试题导入action</b>
 * 
 * @author jack
 * @version 1.0
 * @date 二〇一三年一月十三日 13:20:13
 * 
 */
@At("/comm")
@IocBean(fields = { "dao" })
public class CommondModule extends EntityService<BaseProblem> {
	private static final Log log = Logs.get();

	/**
	 * 生成下载试卷word模板
	 * 
	 * @param request
	 * @param response
	 * @return
	 */
	@At
	@Ok("void")
	public Object loadWord(HttpServletRequest request,
			HttpServletResponse response) {
		try {
			WordDocumentUtil.writeWordDocument("examPageMold.doc", response,
					request);
			return DwzUtil.dialogAjaxDone(DwzUtil.OK);
		} catch (Exception e) {
			if (log.isDebugEnabled())
				log.error("E!!!!!" + e);
			return DwzUtil.dialogAjaxDone(DwzUtil.FAIL);
		}
	}

	/**
	 * 跳转到导入excel页面
	 */
	@At
	@Ok("jsp:page.comm.import")
	public void excelUi() {
	}

	/**
	 * 下载模板
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@At("/downLoad")
	@Ok("void")
	public Object downLoadExcel(@Param("fName") String fileName,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		InputStream is = null;
		OutputStream os = null;
		// 判断文件名是不是为空！
		if (Strings.isEmpty(fileName))
			return DwzUtil.dialogAjaxDone(DwzUtil.FAIL, null, "没有文件可以下载！");

		String fName = URLDecoder.decode(fileName, "UTF-8");
		String filePath = request.getSession().getServletContext()
				.getRealPath("/upload");
		try {
			File file = new File(filePath + "/" + fName);
			response.reset();
			response.addHeader("Content-Disposition", "attachment;filename="
					+ file.getName());
			response.addHeader("Content-Length", "" + file.length());
			/** 数据量 **/
			response.setContentType("application/msexcel");
			// 以流的形式下载
			is = new BufferedInputStream(new FileInputStream(file));
			os = new BufferedOutputStream(response.getOutputStream());
			// 存储
			byte[] buffer = new byte[1024];
			int len = 0;
			while ((len = is.read()) > -1) {
				os.write(buffer, 0, len);
			}
			os.flush();
			return DwzUtil.dialogAjaxDone(DwzUtil.OK);
		} catch (Exception e) {
			if (log.isDebugEnabled())
				log.debug("E!!!!" + e);
			return DwzUtil.dialogAjaxDone(DwzUtil.FAIL);
		} finally {
			if (is != null)
				is.close();
			if (os != null)
				os.close();
		}
	}

	/**
	 * excel导入数据库
	 * 
	 * @param file
	 * @return
	 * @throws IOException
	 */
	@At("/import")
	@AdaptBy(type = UploadAdaptor.class, args = { "ioc:myUpload" })
	public Object excelTo(@Param("filePath") TempFile file,
			@Param("type") String type, @Param("navID") String navTabID)
			throws IOException {
		InputStream is = null;
		Workbook book = null;
		if (!file.getFile().exists())
			return DwzUtil.dialogAjaxDone(DwzUtil.FAIL, null, "文件上传失败！");
		// 创建sql
		Sql sql = Sqls
				.create("INSERT INTO HSE_EXAM_PROBLEM(jtsl,content,aOPtion,bOPtion,cOPtion,dOPtion,type,ndxs,sujecetID,gwID,answer,jsonText,typeID,remark,status)"
						+ " VALUES(@jtsl,@content,@aOPtion,@bOPtion,@cOPtion,@dOPtion,@type,@ndxs,@sujecetID,@gwID,@answer,@jsonText,@typeID,@remark,@status)");
		try {
			is = new FileInputStream(file.getFile());
			// excel表格
			book = Workbook.getWorkbook(is);
			// 获取总列数
			Sheet sheet = book.getSheet(0);
			int columns = sheet.getColumns();
			// 获取多少行
			int rows = sheet.getRows();
			for (int i = 1; i < rows; i++) {
				for (int j = 1; j <= columns; j++) {
					sql.params().set("jtsl", getValue(sheet.getCell(1, i)));
					sql.params().set("content", getValue(sheet.getCell(2, i)));
					sql.params().set("aOPtion", getValue(sheet.getCell(3, i)));
					sql.params().set("bOPtion", getValue(sheet.getCell(4, i)));
					sql.params().set("cOPtion", getValue(sheet.getCell(5, i)));
					sql.params().set("dOPtion", getValue(sheet.getCell(6, i)));
					sql.params().set("type", getValue(sheet.getCell(7, i)));
					sql.params().set("ndxs", getValue(sheet.getCell(8, i)));
					sql.params().set("sujecetID",
							getSubjectID(sheet.getCell(9, i)));
					sql.params().set("gwID", getGwID(sheet.getCell(10, i)));
					sql.params().set("answer", getValue(sheet.getCell(11, i)));
					sql.params().set("jsonText", null);
					sql.params().set("remark", "excel导入");
					sql.params().set("status", "0");

				}
				sql.addBatch();
			}
			this.dao().execute(sql);
			return DwzUtil.dialogAjaxDone(DwzUtil.OK,
					navTabID == null ? "judge" : navTabID);
		} catch (Exception e) {
			if (log.isDebugEnabled())
				log.debug("E!!!!", e);
			return DwzUtil.dialogAjaxDone(DwzUtil.FAIL);
		} finally {
			if (book != null && !"".equals(book))
				book.close();
			if (is != null)
				is.close();
		}
	}

	/**
	 * 获取单元格中内容
	 * 
	 * @param cell
	 * @return
	 */
	public String getValue(Cell cell) {
		return cell.getContents() == null ? "" : cell.getContents();
	}

	/**
	 * 获得岗位ID
	 * 
	 * @param cell
	 * @return
	 */
	public int getGwID(Cell cell) {
		String gwName = (cell.getContents() == null ? "" : cell.getContents());
		if (!Strings.isEmpty(gwName) && !"".equals(gwName)) {
			JobInfos gw = this.dao().fetch(JobInfos.class, gwName);
			if (null == gw || "".equals(gw)) {
				JobInfos job = new JobInfos();
				job.setGwName(gwName);
				job.setRemark("excel导入");
				job.setStatus("0");
				return this.dao().insert(job).getID();
			}
			return gw.getID();
		}
		return 0;
	}

	/**
	 * 获取科目名称
	 * 
	 * @param cell
	 * @return
	 */
	public int getSubjectID(Cell cell) {
		String subName = (cell.getContents() == null ? "" : cell.getContents());
		if (!Strings.isEmpty(subName) && !"".equals(subName)) {
			TrainSubject subject = this.dao()
					.fetch(TrainSubject.class, subName);
			if (null == subject || "".equals(subject)) {
				TrainSubject obj = new TrainSubject();
				obj.setSubName(subName);
				obj.setRemark("excel导入");
				obj.setStatus("0");
				return this.dao().insert(obj).getSubID();
			}
			return subject.getSubID();
		}
		return 0;
	}

}
